package net.suncaper.mvc.mapper.impl;

import net.suncaper.mvc.mapper.ReviewMapper;
import net.suncaper.mvc.model.domain.Review;
import net.suncaper.mvc.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

/**
 * Review表数据库访问接口实现类
 */
public class ReviewMapperImpl implements ReviewMapper {
    private static final QueryRunner RUNNER = new QueryRunner();

    /**
     * 添加评价
     * @param review 评价信息
     * @return 添加结果
     */
    @Override
    public boolean insertReview(Review review) {
        // 修正字段名：统一为下划线命名（ReviewTime→review_time, UserId→user_id, HotelName→hotel_name）
        String sql = "INSERT INTO review(review_time, user_id, rating, content, hotel_name) VALUES (?, ?, ?, ?, ?)";
        Connection connection = JDBCUtils.getConnection();
        int rows = 0;
        try {
            rows = RUNNER.update(
                    connection,
                    sql,
                    review.getReviewTime(), review.getUserId(),
                    review.getRating(), review.getContent(),
                    review.getHotelName()
            );
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return rows > 0;
    }

    /**
     * 根据酒店名称获取评价列表
     * @param hotelName 酒店名称
     * @return 评价列表
     */
    @Override
    public List<Review> selectReviewsByHotelName(String hotelName) {
        // 修正字段名：HotelName→hotel_name, ReviewTime→review_time
        String sql = "SELECT * FROM review WHERE hotel_name = ? ORDER BY review_time DESC";
        Connection connection = JDBCUtils.getConnection();
        List<Review> reviews = new ArrayList<>();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        
        try {
            pstmt = connection.prepareStatement(sql);
            pstmt.setString(1, hotelName);
            rs = pstmt.executeQuery();
            
            while (rs.next()) {
                Review review = new Review();
                review.setReviewId(rs.getLong("review_id"));
                review.setReviewTime(rs.getTimestamp("review_time"));
                review.setUserId(rs.getString("user_id"));
                review.setRating(rs.getBigDecimal("rating"));
                review.setContent(rs.getString("content"));
                review.setHotelName(rs.getString("hotel_name"));
                reviews.add(review);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeResources(rs, pstmt);
            JDBCUtils.close();
        }
        return reviews;
    }

    /**
     * 根据用户ID获取评价列表
     * @param userId 用户ID
     * @return 评价列表
     */
    @Override
    public List<Review> selectReviewsByUserId(String userId) {
        // 修正字段名：UserId→user_id, ReviewTime→review_time
        String sql = "SELECT * FROM review WHERE user_id = ? ORDER BY review_time DESC";
        Connection connection = JDBCUtils.getConnection();
        List<Review> reviews = new ArrayList<>();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        
        try {
            pstmt = connection.prepareStatement(sql);
            pstmt.setString(1, userId);
            rs = pstmt.executeQuery();
            
            while (rs.next()) {
                Review review = new Review();
                review.setReviewId(rs.getLong("review_id"));
                review.setReviewTime(rs.getTimestamp("review_time"));
                review.setUserId(rs.getString("user_id"));
                review.setRating(rs.getBigDecimal("rating"));
                review.setContent(rs.getString("content"));
                review.setHotelName(rs.getString("hotel_name"));
                reviews.add(review);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeResources(rs, pstmt);
            JDBCUtils.close();
        }
        return reviews;
    }
    
    /**
     * 根据评价ID获取评价
     * @param reviewId 评价ID
     * @return 评价信息
     */
    @Override
    public Review selectReviewById(Long reviewId) {
        String sql = "SELECT * FROM review WHERE review_id = ?";
        Connection connection = JDBCUtils.getConnection();
        Review review = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        
        try {
            pstmt = connection.prepareStatement(sql);
            pstmt.setLong(1, reviewId);
            rs = pstmt.executeQuery();
            
            if (rs.next()) {
                review = new Review();
                review.setReviewId(rs.getLong("review_id"));
                review.setReviewTime(rs.getTimestamp("review_time"));
                review.setUserId(rs.getString("user_id"));
                review.setRating(rs.getBigDecimal("rating"));
                review.setContent(rs.getString("content"));
                review.setHotelName(rs.getString("hotel_name"));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeResources(rs, pstmt);
            JDBCUtils.close();
        }
        return review;
    }

    /**
     * 根据评价时间获取评价
     * @param reviewTime 评价时间
     * @return 评价信息
     */
    @Override
    public Review selectReviewByTime(String reviewTime) {
        // 修正字段名：ReviewTime→review_time
        String sql = "SELECT * FROM review WHERE review_time = ?";
        Connection connection = JDBCUtils.getConnection();
        Review review = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        
        try {
            pstmt = connection.prepareStatement(sql);
            pstmt.setString(1, reviewTime);
            rs = pstmt.executeQuery();
            
            if (rs.next()) {
                review = new Review();
                review.setReviewId(rs.getLong("review_id"));
                review.setReviewTime(rs.getTimestamp("review_time"));
                review.setUserId(rs.getString("user_id"));
                review.setRating(rs.getBigDecimal("rating"));
                review.setContent(rs.getString("content"));
                review.setHotelName(rs.getString("hotel_name"));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeResources(rs, pstmt);
            JDBCUtils.close();
        }
        return review;
    }

    /**
     * 更新评价信息
     * @param review 评价信息
     * @return 更新结果
     */
    @Override
    public boolean updateReview(Review review) {
        // 使用review_id作为主键，同时更新review_time字段
        String sql = "UPDATE review SET rating = ?, content = ?, review_time = ? WHERE review_id = ?";
        Connection connection = JDBCUtils.getConnection();
        int rows = 0;
        try {
            rows = RUNNER.update(
                    connection,
                    sql,
                    review.getRating(), review.getContent(),
                    review.getReviewTime(), review.getReviewId()
            );
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return rows > 0;
    }
    
    /**
     * 通过评价ID删除评价
     * @param reviewId 评价ID
     * @return 删除结果
     */
    @Override
    public boolean deleteReviewById(Long reviewId) {
        String sql = "DELETE FROM review WHERE review_id = ?";
        Connection connection = JDBCUtils.getConnection();
        int rows = 0;
        try {
            rows = RUNNER.update(
                    connection,
                    sql,
                    reviewId
            );
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return rows > 0;
    }

    /**
     * 删除评价
     * @param reviewTime 评价时间
     * @return 删除结果
     */
    @Override
    public boolean deleteReview(String reviewTime) {
        // 修正字段名：ReviewTime→review_time
        String sql = "DELETE FROM review WHERE review_time = ?";
        Connection connection = JDBCUtils.getConnection();
        int rows = 0;
        try {
            rows = RUNNER.update(
                    connection,
                    sql,
                    reviewTime
            );
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return rows > 0;
    }

    @Override
    public java.util.List<Review> selectAllReviews() {
        String sql = "SELECT * FROM review ORDER BY review_time DESC";
        Connection connection = JDBCUtils.getConnection();
        List<Review> reviews = new ArrayList<>();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        
        try {
            pstmt = connection.prepareStatement(sql);
            rs = pstmt.executeQuery();
            
            while (rs.next()) {
                Review review = new Review();
                review.setReviewId(rs.getLong("review_id"));
                review.setReviewTime(rs.getTimestamp("review_time"));
                review.setUserId(rs.getString("user_id"));
                review.setRating(rs.getBigDecimal("rating"));
                review.setContent(rs.getString("content"));
                review.setHotelName(rs.getString("hotel_name"));
                reviews.add(review);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeResources(rs, pstmt);
            JDBCUtils.close();
        }
        return reviews;
    }
    
    /**
     * 计算酒店的平均评分
     * @param hotelName 酒店名称
     * @return 平均评分，如果没有评分返回null
     */
    @Override
    public BigDecimal calculateAverageRating(String hotelName) {
        String sql = "SELECT AVG(rating) as avg_rating FROM review WHERE hotel_name = ?";
        Connection connection = JDBCUtils.getConnection();
        BigDecimal avgRating = null;
        
        try {
            avgRating = RUNNER.query(
                    connection,
                    sql,
                    new ScalarHandler<BigDecimal>(),
                    hotelName
            );
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        
        return avgRating;
    }
    
    /**
     * 关闭资源
     * 
     * @param rs ResultSet对象
     * @param stmt PreparedStatement对象
     */
    private void closeResources(ResultSet rs, PreparedStatement stmt) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}